﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL {
    public class Flow_NewWork_DAO {

        /// <summary>
        /// 根据指定条件更新数据表中的信息
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="sqlParas">执行sql语句要传入的参数键值对集合</param>
        /// <returns></returns>
        public int UpdateNewWorkTable(string sql, Hashtable sqlParas)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                sqlCmd = new SqlCommand(sql, sqlConn);
                if (sqlParas.Count > 0)
                {
                    foreach (object key in sqlParas.Keys)
                    {
                        sqlCmd.Parameters.Add(new SqlParameter(key.ToString(), sqlParas[key]));
                    }
                }
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据指定条件查找新建工作信息
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="sqlParas">执行sql语句传入的参数键值对集合</param>
        /// <returns></returns>
        public List<Flow_NewWork> FindNewWorkListByTest(string sql, Hashtable sqlParas)
        {
            List<Flow_NewWork> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                sqlCmd = new SqlCommand(sql, sqlConn);
                if (sqlParas.Count > 0)
                {
                    foreach (object key in sqlParas.Keys)
                    {
                        sqlCmd.Parameters.Add(new SqlParameter(key.ToString(), sqlParas[key]));
                    }
                }
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<Flow_NewWork>();
                    while (sqlDr.Read())
                    {
                        Flow_NewWork flow_newWork = new Flow_NewWork();
                        flow_newWork.NewWork_id = sqlDr["Id"] == null ? -1 : (int)sqlDr["Id"];
                        flow_newWork.NewWork_name = sqlDr["New_Work_Name"] == null ? null : (string)sqlDr["New_Work_Name"];
                        flow_newWork.Base_newFlow_id = sqlDr["New_Flow_Id"] == null ? -1 : (int)sqlDr["New_Flow_id"];
                        flow_newWork.Attchements_id = sqlDr["Attechments_Id"] == null ? null : (string)sqlDr["Attechments_Id"];
                        flow_newWork.Attchements_name = sqlDr["Attechments_Name"] == null ? null : (string)sqlDr["Attechments_Name"];
                        list.Add(flow_newWork);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 清空该流程步骤下的所有工作数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DelAllWorksByFlowId(int id)
        {
            bool flag = false;
            SqlConnection conn = null;
            try
            {
                conn = SQLManager.GetConn();
                SqlCommand sc = new SqlCommand();
                sc.Connection = conn;
                conn.Open();
                sc.CommandText = "delete from Flow_NewWork where New_Flow_Id=" + id.ToString();
                int i = sc.ExecuteNonQuery();
                if (i > 0)
                {
                    flag = true;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return flag;
        }

        /// <summary>
        /// 根据工作编号查找该工作所属流程是否允许上传附件
        /// </summary>
        /// <param name="workid"></param>
        /// <returns></returns>
        public int findFlowAffixFile(int workid)
        {
            int allowaffix = 0;
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader sqldr = null;
            try
            {
                conn = SQLManager.GetConn();
                string sql = "select Allow_AffixFile from flow_newflow where id = (select new_flow_id from flow_newwork where id=@workid)";
                conn.Open();
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@workid", workid));
                sqldr = cmd.ExecuteReader();
                sqldr.Read();
                if (sqldr["Allow_AffixFile"] != null)
                    allowaffix = (int)sqldr["Allow_AffixFile"];
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
                if (sqldr != null)
                {
                    sqldr.Close();
                    sqldr.Dispose();
                }
            }
            return allowaffix;
        }
    }
}
